/**
 * NameDAO.java
 *
 * Enterprise Java
 *
 * Author:  Matthew Braddy
 * Created: Nov 21, 2010
 *
 */

package java113.labs;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
import java113.dataAccess.DAOBase;
import java113.dataAccess.DataManager;

/**
 *
 * @author Matthew Braddy
 */
public class NameDAO extends DAOBase {

    private Properties properties;

    private DataManager m_oDataManager;

    private Name name;

    public NameDAO(Properties properties, DataManager m_oDataManager) throws Exception {

        this.properties = properties;

        this.m_oDataManager = m_oDataManager;
        
        String sqlFileName = this.properties.getProperty("path.sql") + 
                             this.properties.getProperty("sql.file.name");

        this.load(sqlFileName, this.properties.getProperty("webApp"));

    }

    public Name getName(int inId) throws SQLException {

        String strSQL;
        ResultSet resultSet = null;
        Statement statement = null;

        try
        {
            strSQL = this.getSQL ("get name");  //obtain the SQL by name that is loaded in memory

            strSQL = strSQL.replace ("%%ID%%", String.valueOf (inId));

            statement = (Statement) this.m_oDataManager.createStatement ();  //create a statement for our pending transaction
            resultSet = this.m_oDataManager.executeQuery ((java.sql.Statement) statement,strSQL);  //execute the query

            if(resultSet != null)  //defensive coding
            {

                while (resultSet.next()) {

                    name = new Name();

                    int    id                = resultSet.getInt("id");
                    String first             = resultSet.getString("first");
                    String last              = resultSet.getString("last");
                    int    classification_id = resultSet.getInt("classification_id");
                    int    address_id        = resultSet.getInt("address_id");

                    name.setId(id);
                    name.setFirst(first);
                    name.setLast(last);
                    name.setClassification_id(classification_id);
                    name.setAddress_id(address_id);

                }

            }

            this.m_oDataManager.close ((java.sql.Statement) statement);
        }
        catch(Exception Ex)
        {
            Ex.printStackTrace ();
        }

        return name;

    }

    public ArrayList getAllNames() throws SQLException {

        String strSQL;
        ResultSet resultSet = null;
        Statement statement = null;

        ArrayList listOfNames = null;

        try
        {
            strSQL = this.getSQL ("get all names");

            statement = (Statement) this.m_oDataManager.createStatement ();  //create a statement for our pending transaction
            resultSet = this.m_oDataManager.executeQuery ((java.sql.Statement) statement,strSQL);  //execute the query

            if(resultSet != null)  //defensive coding
            {

                listOfNames = new ArrayList();

                while (resultSet.next()) {

                    name = new Name();

                    int    id                = resultSet.getInt("id");
                    String first             = resultSet.getString("first");
                    String last              = resultSet.getString("last");
                    int    classification_id = resultSet.getInt("classification_id");
                    int    address_id        = resultSet.getInt("address_id");

                    name.setId(id);
                    name.setFirst(first);
                    name.setLast(last);
                    name.setClassification_id(classification_id);
                    name.setAddress_id(address_id);

                    listOfNames.add(name);

                }

            }

            this.m_oDataManager.close ((java.sql.Statement) statement);
        }
        catch(Exception Ex)
        {
            Ex.printStackTrace ();
        }

        return listOfNames;

    }

    public int setName(int inId, String inFirst, String inLast, int inClassification_id, int inAddress_id) throws SQLException {

        String strSQL;
        ResultSet keys = null;
        Statement statement = null;
        int key = 0;

        try
        {
            strSQL = this.getSQL ("set name");

//            strSQL = strSQL.replace ("%%ID%%", String.valueOf (inId));
            strSQL = strSQL.replace ("%%FIRST%%", String.valueOf (inFirst));
            strSQL = strSQL.replace ("%%LAST%%", String.valueOf (inLast));
            strSQL = strSQL.replace ("%%CLASSID%%", String.valueOf (inClassification_id));
            strSQL = strSQL.replace ("%%ADDRESSID%%", String.valueOf (inAddress_id));

            statement = this.m_oDataManager.createStatement ();  //create a statement for our pending transaction
            keys = this.m_oDataManager.executeUpdate ((java.sql.Statement) statement,strSQL);  //execute the query

            keys.next();

            key = keys.getInt(1);

            this.m_oDataManager.close ((java.sql.Statement) statement);

        }
        catch(Exception Ex)
        {
            Ex.printStackTrace ();
        }

        return key;

    }

}
